#!/usr/bin/python
import urllib.request
from bs4 import BeautifulSoup
import string
from sqlTools import insert,search,getLines
from getDataFromYahoo import getDataFromYahoo,getStockDetailDataFromYahoo
from getHtml import getHtml

#get exchangelist

def getExchangeList():
	url='http://eoddata.com/'
	soup=getHtml(url)
	if soup is None:
		return

	#get exchange code
	table=soup.find('table',attrs={"class":"quotes"})

	sql_insert="insert into exchange (code ,name) values"
	sql_insertPart="" 
	exchangeMode=[]
	for tr in table.find_all('tr'):
		exchangeMode=[]
		for td in tr.find_all('td'):
			exchangeMode.append(td.string)
		if len(exchangeMode)==0:
			continue
		print(exchangeMode)

	        #check exchange Code is exist or not
		exchangeCode=exchangeMode[0]
		exchangeName=exchangeMode[1]

		sql_getExchangeId="select id from exchange where code='"+exchangeCode+"' ;"		
		#print(sql_getExchangeId)
		if search(sql_getExchangeId)==2:
			print("insert one data")
			sql_insertPart=sql_insertPart+"('"+exchangeCode+"' , '"+exchangeName+"'),"
		
	if len(sql_insertPart)>0:
		print(sql_insert+sql_insertPart[:-1])
		insert(sql_insert+sql_insertPart[:-1]+" ;")










#/stocklist/AMEX.htm
def getStockListOfEachExchange():
	url='http://eoddata.com'
	soup=getHtml(url)
	if soup is None:
		return
	#get exchange stock list
	table=soup.find('table',attrs={"class":"quotes"})
	tags_a=table.find_all('a')
	i=1
	for a in tags_a:
		if i%2!=0:
			#getStockCodeList("/stocklist/",a.string)
			print(a["href"],a.string)
		i=i+1




#		/stocklist/AMEX   AMEX    
def getStockCodeList(stockCodeListUrl,exchangeCode):
	url='http://www.eoddata.com'+stockCodeListUrl
	# get start code 
	start_url=url+exchangeCode+".htm"
	start_soup=getHtml(start_url)
	table=start_soup.find('table',attrs={'class':'lett'})
	
	startCodeRange=[]
	for tr in table.find_all('tr'):
		for td in tr.find_all('td'):
			startCodeRange.append(str.upper(td.string))	
	#print(startCodeRange)
	# get stock code start with A,B.....Z
	for i in startCodeRange:
		#u=url+"/"+"A.htm"
		u=url+exchangeCode+"/"+i+".htm"

		soup=getHtml(u)
		table=soup.find('table',attrs={'class':'quotes'})
		
		#get Exchange id by code
		sql_getExchangeId="select id from exchange where code='"+exchangeCode+"' ;"
		exchangeid=search(sql_getExchangeId)
		# check stock Code Is Exist or not
		sql_checkStockCode=""
		stockCodeIsExist=0
			
		sql_insertStock="insert into stock(code,name,exchange_id) values"
		sql_insertStock_part=''
		stockModle=[]
		for tr in table.find_all('tr'):
			
			stockCodeIsExist=0
			stockModle=[]
				
			for td in tr.find_all('td'):
				#print(td.string)
				stockModle.append(td.string)
			
			if len(stockModle)==0:
				continue
			#print(stockModle,"----------m-----",len(stockModle))
				
			stockCode=stockModle[0]
			if stockModle[1] is None: 			
				stockName=""
			else:
				stockName=stockModle[1]
			exchange_id=str(exchangeid["id"])
			sql_checkStockCode="select * from stock where code ='"+stockCode+"' and exchange_id ="+exchange_id+" ;"
			stockCodeIsExist=search(sql_checkStockCode)
			#if not exist then insert
			if stockCodeIsExist==2:
				#insert stock code to table stock
				sql_insertStock_part=sql_insertStock_part+"('"+stockCode+"',\""+stockName+"\" , "+exchange_id+"),"
		if len(sql_insertStock_part)>0:
			if stockCode=='PAR':
				print(sql_insertStock+sql_insertStock_part[:-1]+" ;")
			insert(sql_insertStock+sql_insertStock_part[:-1]+" ;")							


#get all history data of all stocks
def getStockDetail_allHistoryData():
	#get stock code
	sql_search="select s.id as stockID,e.id as exchangeID,s.code as stockCode ,e.YahooSuffix as YahooSuffix from stock s inner join exchange e where s.exchange_id=e.id and (e.YahooSuffix <> 'x');"
	dicts=getLines(sql_search)
	for dict_item in dicts:
		getDataFromYahoo(dict_item)		
			
	


